package com.fsxgt.datagrid.ds.handler;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.fsxgt.datagrid.ds.bo.ColumnInfo;
import com.fsxgt.datagrid.ds.bo.DatasourceJdbcBO;
import com.fsxgt.datagrid.ds.controller.DatasourceJdbcController;
import com.fsxgt.datagrid.ds.exception.DsException;
import com.fsxgt.datagrid.ds.tool.MetaSqlInterface;
import com.fsxgt.datagrid.ds.utils.ParamUtil;
import com.google.common.collect.Lists;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
@Data
@Slf4j
public class Hbase20xsqlHandler implements DbHandler {

	private Connection conn;
	private MetaSqlInterface metaSqlInterface;
	private DatasourceJdbcBO datasourceJdbcBO;
	private String datasourceType;
	private DatasourceJdbcBO config = null;

	/**
	 * @Title: Hbase20xsqlHandler
	 * @Description: Hbase20xsqlHandler构造函数
	 * @param datasource
	 * @param datasourceJdbcBO
	 * @param metaSqlInterface
	 * @throws DsException
	 * @author liuyi
	 * @date 2024-01-11 08:42:08
	 */
	public Hbase20xsqlHandler(DatasourceJdbcBO datasourceJdbcBO, MetaSqlInterface metaSqlInterface, Boolean isTest)
			throws DsException {

		try {
			
			Properties props = new Properties();
//			props.setProperty("phoenix.query.timeoutMs", "1200000");
//			props.setProperty("phoenix.connection.autoCommit", "true");
//			props.setProperty("hbase.rpc.timeout", "1200000");
//			props.setProperty("hbase.client.scanner.timeout.period", "1200000");

			if (isTest) {
				props.setProperty("zookeeper.recovery.retry", "0");
				props.setProperty("hbase.client.retries.number", "0");
				props.setProperty("phoenix.metadata.rpc.retries.number", "0");
				props.setProperty("hbase.client.retries.number", "0");
				props.setProperty("phoenix.queryserver.zookeeper.acl.username", datasourceJdbcBO.getJdbcUsername());
				props.setProperty("phoenix.queryserver.zookeeper.acl.password", datasourceJdbcBO.getJdbcPassword());
		
			}

//			

//			driver=(Driver)(Class.forName("org.apache.phoenix.jdbc.PhoenixDriver").newInstance());
//			DriverManager.registerDriver(driver);
//			
//			System.out.println("hashcode:"+driver.hashCode());

			conn = DriverManager.getConnection(datasourceJdbcBO.getJdbcUrl(), props);

			this.metaSqlInterface=metaSqlInterface;
			this.datasourceType = datasourceJdbcBO.getDatasource();
			this.config = datasourceJdbcBO;
//			PhoenixDriver d = ((PhoenixDriver)driver);

		} catch (Exception e) {

			throw new DsException(e);
		}
	}

	/**
	 * @Title: getColumnsByTableName
	 * @Description: TODO(描述)
	 * @param schema
	 * @param tableName
	 * @return
	 * @throws Exception
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#getColumnsByTableName(java.lang.String,
	 *      java.lang.String)
	 * @author liuyi
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public List<ColumnInfo> getColumnNames(String schema, String tableName) throws SQLException {
		// TODO Auto-generated method stub
		DatabaseMetaData metaData = null;
		List<ColumnInfo> columnNames = Lists.newArrayList();
		ResultSet rs = null;
		try {
			metaData = conn.getMetaData();
			rs = metaData.getColumns(conn.getCatalog(), null, tableName, "%");
			while (rs.next()) {
				ColumnInfo columnInfo = new ColumnInfo(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"), null);
				columnNames.add(columnInfo);

			}

		} finally {
			rs.close();
		}
		return columnNames;

	}

	/**
	 * @Title: getTableNames
	 * @Description: TODO(描述)
	 * @param schema
	 * @return
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#getTableNames(java.lang.String)
	 * @author liuyi
	 * @throws SQLException
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public List<String> getTableNames(String schema) throws SQLException {
		// TODO Auto-generated method stub
		
		
		DatabaseMetaData metaData = null;
		List<String> tables = new ArrayList<String>();
		ResultSet rs = null;
		try {
			metaData = conn.getMetaData();
			rs = metaData.getTables(conn.getCatalog(), null, "%", new String[] { "TABLE" });
			while (rs.next()) {
				if(schema!=null) {
					if(rs.getString("TABLE_SCHEM").equals(schema)) {
						if(!tables.contains(rs.getString("TABLE_NAME"))){
							tables.add(rs.getString("TABLE_NAME"));

						}
					}
				}
			}
			return tables;

		} finally {
			rs.close();
		}
	}

	/**
	 * @Title: getColumnsBySql
	 * @Description: TODO(描述)
	 * @param querySql
	 * @return
	 * @throws SQLException
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#getColumnsBySql(java.lang.String)
	 * @author liuyi
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public List<ColumnInfo> getColumnsBySql(String querySql) throws SQLException {
		// TODO Auto-generated method stub

		List<String> res = Lists.newArrayList();

		String sql = querySql;

		Statement sta = null;
		ResultSet rs = null;

		try {
			
			sta = conn.createStatement();
			rs = sta.executeQuery(sql);

			
			List<ColumnInfo> list = new ArrayList();

			ResultSetMetaData resultSetMetaData = rs.getMetaData();

			int columnCount = resultSetMetaData.getColumnCount();
			for (int i = 1; i <= columnCount; i++) {

				list.add(new ColumnInfo(resultSetMetaData.getColumnLabel(i), resultSetMetaData.getColumnTypeName(i),
						resultSetMetaData.isNullable(i)));
			}
			return list;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (Exception e) {
			}
			try {
				if (sta != null) {
					sta.close();
				}
			} catch (Exception e) {
			}

		}


		
	}

	/**
	 * @Title: getMaxIdVal
	 * @Description: TODO(描述)
	 * @param tableName
	 * @param key
	 * @return
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#getMaxIdVal(java.lang.String,
	 *      java.lang.String)
	 * @author liuyi
	 * @throws SQLException 
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public Object getMaxIdVal(String tableName, String key, String srcWhere) throws SQLException {
		// TODO Auto-generated method stub
		
		List<String> res = Lists.newArrayList();
		
		String where = "";
		if(srcWhere!=null&&srcWhere.trim().length()>0) {
			where=" where " + srcWhere;
		}
		
		String sql = metaSqlInterface.getMaxId(getPhoenixName(tableName), key)+where;

		return queryForObject(sql);
	}
	
	private String getPhoenixName(String name) {
		if(name.indexOf(".")>0) {
			String[] ss = name.split("\\.");
			return "\""+ss[0]+"\"."+"\""+ss[1]+"\"";
		}else {
			return "\""+name+"\"";
		}
	}

	/**
	 * @Title: getSqlForMaxId
	 * @Description: TODO(描述)
	 * @param sql
	 * @param key
	 * @return
	 * @throws DsException
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#getSqlForMaxId(java.lang.String,
	 *      java.lang.String)
	 * @author liuyi
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public String getMaxSqlForExp(String sql, String exp) throws DsException {
		// TODO Auto-generated method stub
		List<String> list = ParamUtil.getFindStringList(sql, "[\\s\\t]+[Ff][Rr][Oo][Mm][\\s\\t]+");
		if (list.size() == 0) {
			throw new DsException("找不到from关键字");
		}
		sql = "select max(" + exp + ") " + sql.substring(sql.indexOf(list.get(0)));
		return sql;
	}

	/**
	 * @Title: getSqlForCount
	 * @Description: TODO(描述)
	 * @param sql
	 * @return
	 * @throws DsException
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#getSqlForCount(java.lang.String)
	 * @author liuyi
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public String getSqlForCount(String sql) throws DsException {
		// TODO Auto-generated method stub
		return "SELECT COUNT(1) as \"count\" from  (" + sql + ") b";

	}

	/**
	 * @Title: update
	 * @Description: TODO(描述)
	 * @param sql
	 * @param params
	 * @return
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#update(java.lang.String,
	 *      java.util.List)
	 * @author liuyi
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public int update(String sql, List params) {
		// TODO Auto-generated method stub
		return 0;
	}

	/**
	 * @Title: queryForObject
	 * @Description: TODO(描述)
	 * @param sql
	 * @return
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#queryForObject(java.lang.String)
	 * @author liuyi
	 * @throws SQLException 
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public Object queryForObject(String sql) throws SQLException {
		// TODO Auto-generated method stub
		
		Statement sta = null;
		ResultSet rs = null;

		try {
			sta = conn.createStatement();
			rs = sta.executeQuery(sql);
			 
			if(rs.next()) {
				return rs.getObject(1);
			}else {
				return null;
			}
			
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (Exception e) {
			}
			try {
				if (sta != null) {
					sta.close();
				}
			} catch (Exception e) {
			}

		}
		
	}

	/**
	 * @Title: queryForList
	 * @Description: TODO(描述)
	 * @param sql
	 * @param params
	 * @return
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#queryForList(java.lang.String,
	 *      java.util.List)
	 * @author liuyi
	 * @throws SQLException 
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public List<Map<String, Object>> queryForList(String sql, List params) throws SQLException {
		// TODO Auto-generated method stub
		
		List res=new ArrayList();
		
		
		PreparedStatement pre = null;
		ResultSet rs = null;

		try {
			pre = conn.prepareStatement(sql);
			int idx = 0;
			for (Object p : params) {
				pre.setObject(++idx, p);
			}
			rs=pre.executeQuery();
			int count = rs.getMetaData().getColumnCount();
			while(rs.next()) {
				Map result = new HashMap();
				for (int i = 1; i <= count; i++) {
					result.put(rs.getMetaData().getColumnLabel(i), rs.getObject(i));
				}
				res.add(result);
			}
			
			return res;

			
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (Exception e) {
			}
			try {
				if (pre != null) {
					pre.close();
				}
			} catch (Exception e) {
			}

		}
		

	}

	/**
	 * @Title: getDatasourceType
	 * @Description: TODO(描述)
	 * @return
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#getDatasourceType()
	 * @author liuyi
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public String getDatasourceType() {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * @Title: getDatasource
	 * @Description: TODO(描述)
	 * @return
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#getDatasource()
	 * @author liuyi
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public DataSource getDatasource() {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * @Title: getSchemas
	 * @Description: TODO(描述)
	 * @return
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#getSchemas()
	 * @author liuyi
	 * @throws SQLException 
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public List<String> getSchemas() throws SQLException {
		// TODO Auto-generated method stub

		DatabaseMetaData metaData = null;
		List<String> res = new ArrayList<String>();
		ResultSet rs = null;
		try {
			metaData = conn.getMetaData();
			rs = metaData.getTables(conn.getCatalog(), null, "%", new String[] { "TABLE" });
			while (rs.next()) {
				if(!res.contains(rs.getString("TABLE_SCHEM"))) {
					res.add(rs.getString("TABLE_SCHEM"));
				}
			}
			return res;

		} finally {
			rs.close();
		}
		

	}

	/**
	 * @Title: getConfig
	 * @Description: TODO(描述)
	 * @return
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#getConfig()
	 * @author liuyi
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public DatasourceJdbcBO getConfig() {
		return config;
	}

	/**
	 * @Title: runTest
	 * @Description: TODO(描述)
	 * @return
	 * @throws Exception
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#runTest()
	 * @author liuyi
	 * @date 2024-01-12 16:30:17
	 */
	@Override
	public boolean runTest() throws Exception {
		// TODO Auto-generated method stub

		return true;
	}

	/**
	 * @Title: close
	 * @Description: TODO(描述)
	 * @see com.fsxgt.datagrid.ds.handler.DbHandler#close()
	 * @author liuyi
	 * @date 2024-01-16 08:10:50
	 */
	@Override
	public void close() {

	}

}
